/**************************
 * Project: ePoverty
 * Filename: DonationImporter.java
 * Description: Clears and distributes donations that have been approved by the bank processor.
 * Name: Bunna Veth
 * Date: Apr 17, 2012
 **************************/
package epoverty;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.swing.BorderFactory;
import javax.swing.Box;
import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.filechooser.FileNameExtensionFilter;

public class DonationImporter extends JFrame
{
    private final BigDecimal ZERO = BigDecimal.ZERO.setScale(2);
    private final DateFormat sqlDateTimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    private final int ROUNDING_MODE = BigDecimal.ROUND_HALF_UP; //rounds >= 0.50 up, < 0.50 down
    private final String OVERHEAD_QUERY = "SELECT autoRedirectPercent FROM accounts WHERE accountId=1";
    private final int OVERHEAD_ACCOUNT_ID = 1;
    private final int TRIPS_ACCOUNT_ID = 2;
    private final int CHARITY_ACCOUNT_ID = 3;
    private int PENDING = 0;
    private int FAILED = 1;
    private int CLEARED = 2;
    JTextField selectedFileField;
    String selectedFilePath;
    JButton importButton;
    JTextArea textArea;
    JButton cancelButton;
    JButton browseButton;

    //Test Method **************************************************************
    public static void main(String[] args)
    {
        try
        {
            new DBconnection("jdbc:mysql://localhost:3306/epoverty", "com.mysql.jdbc.Driver", "epoverty", "Cis2770#"); //local
            // new DBconnection("jdbc:mysql://50.63.244.60:3306/epoverty", "com.mysql.jdbc.Driver", "epoverty", "Cis2770#"); //goDaddy
        }
        catch (Exception e)
        {
            System.out.println("Connection could not be established.");
        }
        DonationImporter window = new DonationImporter();
        window.display();
    }

    //**************************************************************************
    // INITIALIZE GUI ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    //**************************************************************************
    public DonationImporter()
    {
        super("Import Donations File");
    }

    public void display()
    {

        //file selection panel
        Box fileSelectionPanel = Box.createHorizontalBox();
        selectedFileField = new JTextField();
        selectedFileField.setEnabled(false);

        browseButton = new JButton("Browse...");
        browseButton.addActionListener(new ActionListener()
        {
            @Override
            public void actionPerformed(ActionEvent ae)
            {
                //initialize file chooser
                JFileChooser fileChooser = new JFileChooser();
                FileNameExtensionFilter filter = new FileNameExtensionFilter("CSV File", "csv");
                fileChooser.setFileFilter(filter);

                int returnValue = fileChooser.showOpenDialog(null);

                //successful selection
                if (returnValue == JFileChooser.APPROVE_OPTION)
                {
                    selectedFileField.setText(fileChooser.getSelectedFile().getName());
                    selectedFilePath = fileChooser.getSelectedFile().getAbsolutePath();
                    importButton.setEnabled(true);
                }
                //failed selection
                else
                {
                    selectedFileField.setText("");
                    selectedFilePath = null;
                    importButton.setEnabled(false);
                }
            }
        });

        fileSelectionPanel.add(selectedFileField);
        fileSelectionPanel.add(browseButton);
        add(fileSelectionPanel, BorderLayout.NORTH);

        //instructions
        textArea = new JTextArea("Please select a file to import. "
                + "Only import files with the \".csv\" extension (comma-separated values). "
                + "The columns should be ordered as follows: Donation ID, Donation Amount, Cleared Status. ");
        textArea.setLineWrap(true);
        textArea.setWrapStyleWord(true);
        textArea.setEnabled(false);
        textArea.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));
        add(textArea, BorderLayout.CENTER);

        //import panel
        JPanel importPanel = new JPanel(new FlowLayout());
        importButton = new JButton("Import");
        importButton.setEnabled(false);
        importButton.addActionListener(new ActionListener()
        {
            @Override
            public void actionPerformed(ActionEvent ae)
            {
                textArea.setText("Importing donations...");

                //disable components
                browseButton.setEnabled(false);
                selectedFileField.setEnabled(false);
                importButton.setVisible(false);
                cancelButton.setEnabled(false);

                //begin importing
                ImportProcessorData importer = new ImportProcessorData();
                importer.parseFile(selectedFilePath);
                bulkDistribution(importer.dataArray);
                moveFile();

                //completion notification
                cancelButton.setText("OK");
                cancelButton.setEnabled(true);
                textArea.setText("File import complete!");
            }
        });
        importPanel.add(importButton);

        cancelButton = new JButton("Cancel");
        cancelButton.addActionListener(new ActionListener()
        {
            @Override
            public void actionPerformed(ActionEvent ae)
            {
                dispose();
            }
        });
        importPanel.add(cancelButton);

        add(importPanel, BorderLayout.SOUTH);

        //jframe settings
        setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        setSize(300, 200);
        setLocationRelativeTo(null);
        setVisible(true);
    }

    //**************************************************************************
    //Distribution Process ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    //**************************************************************************
    public void bulkDistribution(ProcessorData[] donations)
    {
        //retrieve overhead percent
        BigDecimal percentOverhead = fetchDecimal(OVERHEAD_QUERY);

        for (ProcessorData donation : donations)
        {
            int donationId = donation.donationId;
            BigDecimal processedDonationAmount = donation.donationAmount;
            int clearedStatus = donation.statusID;

            System.out.printf("donationId=%s; amount=%s; code=%s", donationId, processedDonationAmount, clearedStatus);

            //process code check
            String processQuery = "SELECT processingCode FROM donations WHERE donationId=" + donationId;
            int processingCode = fetchInt(processQuery);
            System.out.println("\nCurrent Processing Code = " + processingCode);
            if (processingCode != PENDING)
            {
                System.out.printf("Donation %s has already been processed\n\n", donationId);
                continue;
            }

            //Failed
            if (clearedStatus == FAILED)
            {
                System.out.println("Donation Failed.");
                clearDonation(donationId, FAILED); //should we remove fundraiser id?
                System.out.println();
                continue;
            }

            //Cleared
            if (clearedStatus == CLEARED)
            {
                System.out.println("Donation Cleared.");

                //gather fundraiser info
                BigDecimal totalDonationAmount = fetchDecimal("SELECT donationAmount FROM donations WHERE donationId=" + donationId);
                int fundraiserId = fetchInt("SELECT fundraiserId FROM donations WHERE donationId=" + donationId);
                boolean hasFundraiser = (fundraiserId > 0);
                BigDecimal raised = ZERO;
                BigDecimal goal = ZERO;

                //get stats if donation is tied to a fundraiser
                if (hasFundraiser)
                {
                    raised = fetchDecimal("SELECT raised FROM fundraisers WHERE fundraiserId=" + fundraiserId);
                    goal = fetchDecimal("SELECT raiseGoal FROM fundraisers WHERE fundraiserId=" + fundraiserId);
                }

                //calculate amounts
                BigDecimal[] deposits = calculateDistribution(totalDonationAmount, percentOverhead, raised, goal);
                BigDecimal processingFee = totalDonationAmount.subtract(processedDonationAmount);

                //update database
                updateDepositsTable(donationId, deposits);
                updateAccountsTable(deposits);
                withdrawProcessingFee(donationId, processingFee);
                if (hasFundraiser)
                    creditFundraiser(fundraiserId, totalDonationAmount, raised, goal);
                clearDonation(donationId, CLEARED);
            }
            System.out.println();
        }
    }//end bulkDistribution()

    //**************************************************************************
    // DATABASE RETRIEVAL ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    //**************************************************************************
    //Fetch a single value from the database
    public String fetchString(String query)
    {
        String value = null;
        try
        {
            //establish connection and execute query
            Connection connection = DBconnection.getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);

            //retrieve value
            resultSet.next();
            value = resultSet.getString(1);
        }
        catch (Exception e)
        {
            System.out.println("\tFETCH ERROR: " + query);
            System.out.println("\t" + e);
        }

        return value;
    }//end fetchString()

    //Helper methods to convert to a specific type
    public int fetchInt(String query)
    {
        String value = fetchString(query);

        if (value == null)
            return -1;
        else
            return Integer.parseInt(value);
    }

    public double fetchDouble(String query)
    {
        String value = fetchString(query);

        if (value == null)
            return -1;
        else
            return Double.parseDouble(value);
    }

    public BigDecimal fetchDecimal(String query)
    {
        String value = fetchString(query);

        if (value == null)
            return new BigDecimal("-1");
        else
            return new BigDecimal(value).setScale(2);
    }

    //**************************************************************************
    // DEPOSITS CALCULATION ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    //**************************************************************************
    public BigDecimal[] calculateDistribution(BigDecimal donationAmount, BigDecimal percentOverhead, BigDecimal raiseAmount, BigDecimal goalAmount)
    {
        //initialize deposit values
        BigDecimal overheadDeposit;
        BigDecimal tripsDeposit;
        BigDecimal charityDeposit;

        //goal indicators
        boolean hasAlreadyMetGoal = (raiseAmount.compareTo(goalAmount) >= 0);
        boolean willStayBelowGoal = (raiseAmount.add(donationAmount).compareTo(goalAmount) < 0);

        //(1) fundraiser has met their goal (remainder goes to charity)
        if (hasAlreadyMetGoal)
        {
            overheadDeposit = donationAmount.multiply(percentOverhead).setScale(2, ROUNDING_MODE);
            tripsDeposit = ZERO;
            charityDeposit = donationAmount.subtract(overheadDeposit).setScale(2, ROUNDING_MODE);
        }
        //(2) donation will stay below goal (remainder goes to trips)
        else if (willStayBelowGoal)
        {
            overheadDeposit = donationAmount.multiply(percentOverhead).setScale(2, ROUNDING_MODE);
            tripsDeposit = donationAmount.subtract(overheadDeposit).setScale(2, ROUNDING_MODE);
            charityDeposit = ZERO;
        }
        //(3) donation WILL meet or exceed goal (distribute among all 3 accounts)
        else
        {
            BigDecimal percentRemaining = BigDecimal.ONE.subtract(percentOverhead);
            BigDecimal actualDifference = goalAmount.subtract(raiseAmount).multiply(percentRemaining);

            overheadDeposit = donationAmount.multiply(percentOverhead).setScale(2, ROUNDING_MODE);
            tripsDeposit = actualDifference.setScale(2, ROUNDING_MODE);
            charityDeposit = donationAmount.subtract(overheadDeposit).subtract(tripsDeposit).setScale(2, ROUNDING_MODE);
        }

        //return deposits array
        System.out.printf("donation=%s; overhead=%s; raised=%s; goal=%s\n", donationAmount, percentOverhead, raiseAmount, goalAmount);
        System.out.printf("%s --> overhead; %s --> trips; %s --> charity\n", overheadDeposit, tripsDeposit, charityDeposit);
        BigDecimal[] deposits =
        {
            overheadDeposit, tripsDeposit, charityDeposit
        };
        return deposits;
    }//end calculateDistribution()

    //**************************************************************************
    // UPDATE DATABASE TABLES ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    //**************************************************************************
    //Update Deposits Table
    private void updateDepositsTable(int donationId, BigDecimal[] deposits)
    {
        for (int i = 0; i < deposits.length; i++)
        {
            BigDecimal deposit = deposits[i];
            int accountId = i + 1;

            //add deposit to table
            if (deposit.compareTo(ZERO) > 0)
            {
                String update = String.format("INSERT INTO deposits (%s) VALUES (%s, %s, %s)",
                                              "accountId, donationId, amount",
                                              accountId, donationId, deposit);
                executeUpdate(update);
            }
        }
    }

    //Update Accounts
    private void updateAccountsTable(BigDecimal[] deposits)
    {
        for (int i = 0; i < deposits.length; i++)
        {
            BigDecimal deposit = deposits[i];
            int accountId = i + 1;

            //add deposit to current balance
            if (deposit.compareTo(ZERO) > 0)
            {
                BigDecimal balance = fetchDecimal("SELECT balance FROM accounts WHERE accountId=" + accountId);
                balance = balance.add(deposit);
                String depositUpdate = String.format("UPDATE accounts SET balance=%s WHERE accountId=%s",
                                                     balance, accountId);
                executeUpdate(depositUpdate);
            }
        }
    }

    //Withdraw Processing Fee
    private void withdrawProcessingFee(int donationId, BigDecimal fee)
    {
        //exit if donation or processing fee don't exist
        if (donationId < 1 || fee.compareTo(ZERO) < 1)
            return;

        //withdraw processing fee
        String payee = "Processing Fee";
        String description = String.format("Donation #%s processing fee.", donationId);
        String withdrawalUpdate = String.format("INSERT INTO withdrawals (%s) VALUES (%s, %s, '%s', '%s', '%s')",
                                                "accountId, amount, payee, description, withdrawalDate",
                                                OVERHEAD_ACCOUNT_ID, fee, payee, description, getCurrentTime());
        executeUpdate(withdrawalUpdate);

        //update overhead account
        BigDecimal overheadBalance = fetchDecimal("SELECT balance FROM accounts WHERE accountId=1");
        BigDecimal newOverheadBalance = overheadBalance.subtract(fee);
        String overheadUpdate = String.format("UPDATE accounts SET balance=%s WHERE accountId=1", newOverheadBalance);
        executeUpdate(overheadUpdate);
    }

    //Credit Fundraiser
    private void creditFundraiser(int id, BigDecimal donationAmount, BigDecimal raisedAmount, BigDecimal raisedGoal)
    {
        //update amount raised
        raisedAmount = raisedAmount.add(donationAmount);
        String amountRaisedUpdate = String.format("UPDATE fundraisers SET raised=%s WHERE fundraiserId=%s", raisedAmount, id);
        executeUpdate(amountRaisedUpdate);

        //update the time the goal is met
        String dateMetRaiseGoal = fetchString("SELECT dateMetRaiseGoal FROM fundraisers WHERE fundraiserId=" + id);
        boolean hasNotAlreadyMetGoal = (dateMetRaiseGoal == null);
        boolean willMeetGoal = (raisedAmount.compareTo(raisedGoal) >= 0);
        if (willMeetGoal && hasNotAlreadyMetGoal)
        {
            String dateMetUpdate = String.format("UPDATE fundraisers SET dateMetRaiseGoal='%s' WHERE fundraiserId=%s", getCurrentTime(), id);
            executeUpdate(dateMetUpdate);
        }
    }

    //Clear Donation
    private void clearDonation(int donationId, int code)
    {
        String processUpdate = String.format("UPDATE donations SET processingCode=%s WHERE donationId=%s", code, donationId);
        executeUpdate(processUpdate);
    }

    private String getCurrentTime()
    {
        return sqlDateTimeFormat.format(new Date());
    }

    //Execute Updates **********************************************************
    private void executeUpdate(String update)
    {
        System.out.println(update);

        try
        {
            Connection connection = DBconnection.getConnection();
            Statement statement = connection.createStatement();
            statement.execute(update);
        }
        catch (Exception e)
        {
            System.out.println("\tUpdate Error: " + update);
            System.out.println("\t" + e);
        }
    }

    //Move File ****************************************************************
    private void moveFile()
    {
        File file = new File(selectedFilePath);

        //destination directory (system-independent path)
        String home = System.getProperty("user.home");
        String slash = System.getProperty("file.separator");
        File importedDirectory = new File(home + slash + "epoverty" + slash + "importedfiles");
        if (!importedDirectory.isDirectory())
            importedDirectory.mkdirs(); //make non-existent directories as necessary
        System.out.printf("Moving file to %s\n", importedDirectory);

        //move file to new directory
        boolean success = file.renameTo(new File(importedDirectory, file.getName()));
        if (success)
            System.out.println("File successfully moved.");
        else
            System.out.println("File could not be moved.");
    }
}//end class